﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Way.Bus.Controllers;
using System.Data;
using Way.EAP.DataAccess.Data;
using Way.EAP.DataAccess.Regulation;
using Way.Contract.Data.ControllerData;
using Way.Bus.WMS.Model;

namespace Way.Bus.WMS.ST.Controllers.Reports
{
    class InOutOrderTotalController : ReportBaseController
    {
        [WhereParameter]
        public string mcode { set; get; }

        [WhereParameter]
        public string customername { set; get; }

        [WhereParameter]
        public string fmodel { set; get; }

        [WhereParameter]
        public string fname { set; get; }

        [WhereParameter]
        public string warehousename { set; get; }

        [WhereParameter]
        public string batchno { set; get; }

        [WhereParameter]
        public string senbatchno { set; get; }


        //[WhereParameter]
        //public bool t3 { set; get; }
        public override System.Data.DataTable GetTable(string tableName, List<EAP.DataAccess.Regulation.SQLWhere> whereList)
        {
            /* sql 记录 
             
       select infotb.materialcode,goods.fname, goods.code mcode,goods.fmodel,c.customername,ck.warehousename,infotb.towhno,infotb.batchno,infotb.senbatchno,infotb.kcnum, infotb.zynum,infotb.kynum, case when inordertb.innum is null then 0 else inordertb.innum end innum, case when outordertb.outnum is null then 0 else outordertb.outnum end outnum
    from
       (
		select  kctb.mid materialcode,kctb.wid towhno,kctb.batchno,kctb.subno senbatchno,sum(kctb.num) kcnum,sum(kctb.kynum) kynum,sum(kctb.zynum) zynum  from 
               (
                ----------------StockInfoView.GetStockSQL(0,0,0,0,"","") ------------------------
                select *,(a.num-a.zynum) kynum from (
                select a.*,case when b.NUM is null then 0 else b.num end zynum from (
                select b.*,a.ID,a.BATCHNO batchno,a.SENBATCHNO subno,a.WAREHOUSEID wid,c.Code wcode, c.WAREHOUSENAME wname, a.DEPOTWBS did,d.code dcode,e.WAREHOUSENAME WAREHOUSENAME,a.NUM num,a.STATUS,a.LASTINTIME ctime,a.Remark remark from
                Acc_WMS_InfoMaterials a
                inner join (select a.STAY10 kid,c.customername kname,a.ClassID lid,b.ClassName lname,a.ID mid,a.code mcode,a.FNAME mname,a.FMODEL model,a.remark spmark from Acc_Bus_BusinessCommodity a
                left join Acc_Bus_CommodityClass b
                on a.ClassID=b.ID
                left join Acc_Bus_BusinessCustomer c
                on a.STAY10=c.ID) b
                on a.Code=b.mid
                left join Acc_WMS_WareHouse c
                on a.WAREHOUSEID=c.id
                left join Acc_WMS_WareHouse d
                on a.DEPOTWBS=d.id left join (select a.id,b.WAREHOUSENAME from Acc_WMS_WareHouse a
                left join Acc_WMS_WareHouse b
                on b.ID=a.PARENTID) e
                on a.DEPOTWBS=e.id ) a
                left join (select a.MATERIALCODE mid,a.BATCHNO batchno,a.DEPOTWBS did,a.senbatchno subno,SUM(a.num) NUM from Acc_WMS_OutOrderMaterials a,
                Acc_WMS_OutOrder b where a.PARENTID=b.ID and b.IsSubmited=0 and a.PARENTID<>0
                group by a.MATERIALCODE,a.BATCHNO,a.DEPOTWBS,a.senbatchno) b
                on a.mid=b.mid and a.did=b.did and a.batchno=b.batchno and a.subno=b.subno
                ) a
             ----------------StockInfoView.GetStockSQL(0,0,0,0,"","")-------------------------------- 
              ) kctb group by  kctb.mid,kctb.wid,kctb.batchno,kctb.subno 
      ) infotb 
       left join 
      (
		select tb.materialcode,inorder.towhno,tb.batchno,tb.senbatchno,  sum(tb.num) innum from  Acc_WMS_InOrderMaterials tb ,Acc_WMS_InOrder inorder where tb.parentid = inorder.id and inorder.issubmited='1' and tb.materialcode is not null group by tb.materialcode,inorder.towhno,tb.batchno,tb.senbatchno
      ) inordertb
      
		on inordertb.materialcode = infotb.materialcode and inordertb.towhno = infotb.towhno and inordertb.batchno = infotb.batchno and inordertb.senbatchno = infotb.senbatchno
		left join 
      (
		select tb.materialcode,outorder.towhno,tb.batchno,tb.senbatchno, sum(tb.num) outnum from  Acc_WMS_outOrderMaterials tb ,Acc_WMS_OutOrder outorder where tb.parentid = outorder.id and outorder.issubmited=1 and tb.materialcode is not null group by tb.materialcode,outorder.towhno,tb.batchno,tb.senbatchno
      ) outordertb      
		on infotb.materialcode = outordertb.materialcode and infotb.towhno = outordertb.towhno  and outordertb.batchno = infotb.batchno and outordertb.senbatchno = infotb.senbatchno
				
		left join Acc_WMS_WareHouse ck on  infotb.towhno = ck.id
		left join Acc_Bus_BusinessCommodity goods on  infotb.materialcode = goods.id
		left join Acc_Bus_BusinessCustomer c  on goods.STAY10=c.ID       
             
             */

            string infoSql = StockInfoView.GetStockSQL(0,0,0,0,"","");
            string sql = " (  select infotb.materialcode,goods.fname, goods.code mcode,goods.fmodel,c.customername,ck.warehousename,infotb.towhno,infotb.batchno,infotb.senbatchno,infotb.kcnum, infotb.zynum,infotb.kynum, case when inordertb.innum is null then 0 else inordertb.innum end innum, case when outordertb.outnum is null then 0 else outordertb.outnum end outnum from "
                + " ( select  kctb.mid materialcode,kctb.wid towhno,kctb.batchno,kctb.subno senbatchno,sum(kctb.num) kcnum,sum(kctb.kynum) kynum,sum(kctb.zynum) zynum  from  "
                + "   ("
                + infoSql
                + "    ) kctb group by  kctb.mid,kctb.wid,kctb.batchno,kctb.subno"
                + "  ) infotb"
                + " left join "
                + " ( select tb.materialcode,inorder.towhno,tb.batchno,tb.senbatchno,  sum(tb.num) innum from  Acc_WMS_InOrderMaterials tb ,Acc_WMS_InOrder inorder where tb.parentid = inorder.id and inorder.issubmited='1' and tb.materialcode is not null group by tb.materialcode,inorder.towhno,tb.batchno,tb.senbatchno  "
                + " ) inordertb"
                + " on inordertb.materialcode = infotb.materialcode and inordertb.towhno = infotb.towhno and inordertb.batchno = infotb.batchno and inordertb.senbatchno = infotb.senbatchno"
                + "   left join"
                + " ( select tb.materialcode,outorder.towhno,tb.batchno,tb.senbatchno, sum(tb.num) outnum from  Acc_WMS_outOrderMaterials tb ,Acc_WMS_OutOrder outorder where tb.parentid = outorder.id and outorder.issubmited=1 and tb.materialcode is not null group by tb.materialcode,outorder.towhno,tb.batchno,tb.senbatchno "
                + " ) outordertb"
                + "   on infotb.materialcode = outordertb.materialcode and infotb.towhno = outordertb.towhno  and outordertb.batchno = infotb.batchno and outordertb.senbatchno = infotb.senbatchno"
                + "  left join Acc_WMS_WareHouse ck on  infotb.towhno = ck.id"
                + " left join Acc_Bus_BusinessCommodity goods on  infotb.materialcode = goods.id"
                + " left join Acc_Bus_BusinessCustomer c  on goods.STAY10=c.ID"
                + " ) tb";
          
            DataTable t = base.GetTable(sql, whereList);
            //IDataAction action = this.model.GetDataAction();
            //loadItem item = new loadItem();
            //item.whereList = whereList.ToArray();
            //string where=this.GetWhereString(
            //if (where != "")
            //    where = "where " + where;
            
            //string sql = "select * from v_employeekpireport " + where;
            //t = action.GetDataTable(sql);
            return t;
        }
        protected override void OnSetParam(WhereParameter param)
        {
            if (param.field == "mcode")
            {
                param.title = "商品编号";
            }
            if (param.field == "fmodel")
            {
                param.title = "规格型号";
            }

            if (param.field == "fname")
            {
                param.title = "商品名称";
            }

            if (param.field == "warehousename")
            {
                param.title = "仓库";
            }

            if (param.field == "batchno")
            {
                param.title = "批次";
            }

            if (param.field == "senbatchno")
            {
                param.title = "有效期";
            }

            if (param.field == "customername")
            {
                param.title = "客户名称";
            }
            
            //if (param.field == "outtime")
            //{
            //    param.title = "是否";
            //    param.wType = WhereParameter.WhereType.ComBox;
            //    param.comboxvalue = "[{ \"Text\": \"否\", \"Value\": 0 }, { \"Text\": \"是\", \"Value\": 1}]";
            //}
        }
    }
}